package cn.com.ieds.dao;

import cn.com.ieds.util.DbUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class RoleDao {
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet rs;
    private String sql;

    // 通过用户id获取角色
    public void getRoleById(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        int id = Integer.parseInt(request.getParameter("id"));
        sql = "select user_re_role.role_id,role_name from user_re_role,tbl_role " +
                "where user_id = ? and user_re_role.role_id = tbl_role.role_id";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            rs = preparedStatement.executeQuery();
            Map<String, Object> json = new HashMap<>();
            if (rs.next()) {
                json.put("role_id", rs.getInt("user_re_role.role_id"));
                json.put("role_name", rs.getString("role_name"));
                response.getWriter().write(JSONObject.fromObject(json).toString());
            }
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 获取所有角色 [{role_id,role_name,role_num},...]
    public void getAllRole(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        String innerSql;
        ResultSet innerRs;
        sql = "select role_id,role_name from tbl_role";
        try {
            preparedStatement = connection.prepareStatement(sql);
            rs = preparedStatement.executeQuery();
            List<Object> json = new ArrayList<>();
            Map<String, Object> info;
            while (rs.next()) {
                info = new HashMap<>();
                innerSql = "select count(user_id) from user_re_role where role_id = ? group by role_id";
                preparedStatement = connection.prepareStatement(innerSql);
                preparedStatement.setInt(1, rs.getInt("role_id"));
                innerRs = preparedStatement.executeQuery();
                if (innerRs.next()) {
                    info.put("role_num", innerRs.getInt(1));
                }else {
                    info.put("role_num", 0);
                }
                info.put("role_id", rs.getInt("role_id"));
                info.put("role_name", rs.getString("role_name"));
                json.add(info);
            }
            response.getWriter().write(JSONArray.fromObject(json).toString());
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 通过角色id获取权限的id
    public void getPermissionsIdByRole(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        sql = "select permissions_id from role_re_permissions where role_id = ?";
        int roleId = Integer.parseInt(request.getParameter("role_id"));
        List<Integer> permissions_id = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,roleId);
            rs = preparedStatement.executeQuery();
            while(rs.next()){
                permissions_id.add(rs.getInt("permissions_id"));
            }
            response.getWriter().write(permissions_id.toString());
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 新建角色，并且赋予对应的权限
    public void addRole(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        try {
            connection.setAutoCommit(false);
            sql = "insert into tbl_role values(null,?)";
            String roleName = request.getParameter("role_name");
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, roleName);
            preparedStatement.execute();

            sql = "select role_id from tbl_role where role_name = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, roleName);
            rs = preparedStatement.executeQuery();
            int roleId = -1;
            if (rs.next()) {
                roleId = rs.getInt("role_id");
            }

            String permissionsList = request.getParameter("permissions_list");
            permissionsList = permissionsList.substring(1);
            permissionsList = permissionsList.substring(0, permissionsList.length() - 1);
            Set<Integer> set = new HashSet<>();
            for (String item : permissionsList.split(",")) {
                set.add(Integer.parseInt(item.substring(1, item.indexOf('|'))));
                set.add(Integer.parseInt(item.substring(item.indexOf('|')+1,item.length()-1)));
            }
            for (Integer item : set) {
                sql = "insert into role_re_permissions values(null,?,?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, roleId);
                preparedStatement.setInt(2, item);
                preparedStatement.execute();
            }
            connection.commit();
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 修改角色
    public void updateRole(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        int roleId = Integer.parseInt(request.getParameter("role_id"));
        String roleName = request.getParameter("role_name");
        try {
            connection.setAutoCommit(false);
            // 更新角色姓名
            sql = "update tbl_role set role_name = ? where role_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,roleName);
            preparedStatement.setInt(2,roleId);
            preparedStatement.execute();

            // 删除原本的权限关系
            sql = "delete from role_re_permissions where role_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,roleId);
            preparedStatement.execute();

            // 加入修改后的权限关系
            String permissionsList = request.getParameter("permissions_list");
            permissionsList = permissionsList.substring(1);
            permissionsList = permissionsList.substring(0, permissionsList.length() - 1);
            Set<Integer> set = new HashSet<>();
            for (String item : permissionsList.split(",")) {
                set.add(Integer.parseInt(item.substring(1, item.indexOf('|'))));
                set.add(Integer.parseInt(item.substring(item.indexOf('|')+1,item.length()-1)));
            }
            for (Integer item : set) {
                sql = "insert into role_re_permissions values(null,?,?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, roleId);
                preparedStatement.setInt(2, item);
                preparedStatement.execute();
            }
            connection.commit();

        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    // 删除角色并清除对应的权限关系
    public void delRole(HttpServletRequest request, HttpServletResponse response) throws SQLException {
        connection = DbUtil.getInstace().getConnection();
        int roleId = Integer.parseInt(request.getParameter("role_id"));
        try {
            connection.setAutoCommit(false);
            // 删除角色
            sql = "delete from tbl_role where role_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,roleId);
            preparedStatement.execute();

            // 删除角色对应的权限关系
            sql = "delete from role_re_permissions where role_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,roleId);
            preparedStatement.execute();

            // 将对应角色的用户还原成游客状态
            sql = "update user_re_role set role_id = 2 where role_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,roleId);
            preparedStatement.execute();
            connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }
}
